package cn.ac.iie.di.ban.data.exchange.runner.slave.task.checkTask;

import cn.ac.iie.di.ban.data.exchange.runner.commons.protocol.ScopeEnum;
import cn.ac.iie.di.ban.data.exchange.runner.commons.protocol.StatusEnum;
import cn.ac.iie.di.ban.data.exchange.runner.commons.protocol.task.order.TaskOrderRequest;
import cn.ac.iie.di.ban.data.exchange.runner.commons.protocol.task.order.TaskOrderResponse;
import cn.ac.iie.di.ban.data.exchange.runner.slave.helper.HttpHelper.HttpHelper;
import cn.iie.jsqlparser.helper.JSQLParserHelper;
import cn.iie.jsqlparser.helper.data.CondTreeData;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;

/**
 * Created by Black-Laptop on 12/9/2017.
 */
public class CheckSQLAndPermissionTask {

    private static final Logger LOG = LoggerFactory.getLogger(CheckSQLAndPermissionTask.class);

    public static String postResult;
    private static String POST_CHANGE_NAME_URL = "http://192.168.11.96:10240/data/";
    private static String POST_GET_SOURCE_ITEM_URL = "http://192.168.11.94:10086/get_agency_source_item/";
    private static String POST_GET_AGENCY_PROVINCE_URL = "http://192.168.11.94:10086/get_agency_province/";

    private static String ITEM_TYPE = "showcolumn";
    private static String POST_RESULT_STATUS = "status";
    private static String POST_RESULT_STATUS_SUCCESS = "succeeded";
    private static String POST_RESULT_DETAIL = "detail";
    private static String POST_RESULT_DATA = "data";
    private static String PROVINCE_COLUMN = "province";

    private static String WHERE_SEARCH_KEY = "search()";

    private String sql;
    private String uid;

    private String errorMsg;
    private String sqlBack;
    private ScopeEnum scop;
    private JSQLParserHelper jsqlParserHelper;

    private String uniqKey;

    public CheckSQLAndPermissionTask(TaskOrderRequest taskOrderRequest) {

        this.sql = taskOrderRequest.getSql();
        this.uid = taskOrderRequest.getUid();
        this.scop = taskOrderRequest.getScope();

        this.errorMsg = "";
        this.sqlBack = "";
    }

    public TaskOrderResponse checkSQLAndPermission(String uniqKey) throws Exception {

        this.uniqKey = uniqKey;
        TaskOrderResponse taskOrderResponse = null;

        Boolean sqlRight = checkSQLRight();
        Boolean permissionRight = checkPermissionRight();

        Boolean sqlRightAndPermissionSuccess = sqlRight && permissionRight;
        if (sqlRightAndPermissionSuccess) {
            taskOrderResponse = setSqlRightAndPermissionSuccess();
            return taskOrderResponse;
        }

        Boolean sqlWrongAndPermissionSuccess = !sqlRight && permissionRight;
        if (sqlWrongAndPermissionSuccess) {
            taskOrderResponse = setSqlWrongAndPermissionSuccess();
            return taskOrderResponse;
        }

        Boolean sqlRightAndPermissionFail = sqlRight && !permissionRight;
        if (sqlRightAndPermissionFail) {
            taskOrderResponse = setSqlRightAndPermissionFail();
            return taskOrderResponse;
        }

        Boolean sqlWrongAndPermissionFail = !sqlRight && !permissionRight;
        if (sqlWrongAndPermissionFail) {

            taskOrderResponse = setSqlWrongAndPermissionFail();
            return taskOrderResponse;
        }

        return taskOrderResponse;
    }

    private TaskOrderResponse setSqlRightAndPermissionSuccess() {

        return new TaskOrderResponse(uniqKey, StatusEnum.CHECK_SUCCESS, sqlBack);
    }

    private TaskOrderResponse setSqlWrongAndPermissionSuccess() {

        return new TaskOrderResponse(uniqKey, StatusEnum.CHECK_SQL_ERROR, errorMsg);
    }

    private TaskOrderResponse setSqlRightAndPermissionFail() {

        return new TaskOrderResponse(uniqKey, StatusEnum.CHECK_PERMISSION_ERROR, errorMsg);
    }

    private TaskOrderResponse setSqlWrongAndPermissionFail() {

        return new TaskOrderResponse(uniqKey, StatusEnum.CHECK_SQL_AND_PERMISSION_ERROR, errorMsg);
    }

    private Boolean checkSQLRight() {
        try {
            jsqlParserHelper = new JSQLParserHelper(this.sql);
        } catch (Exception e) {
            errorMsg += "sql error";
            return false;
        }
        try {
            if (ScopeEnum.MEDIA != this.scop) {
                //测试
                replaceSQLWithNewName();
            } else {
                replaceSQLTypeMedia();
            }
            sqlBack = jsqlParserHelper.getSQLString();
        } catch (Exception e) {
            errorMsg += ExceptionUtils.getFullStackTrace(e);
            return false;
        }
        return true;
    }

    private void replaceSQLTypeMedia() throws Exception {
        String whereKey = jsqlParserHelper.getSQLWhereKeys().get(0);
        String whereValue = jsqlParserHelper.getSQLWhereValues().get(0);
//        whereValue="womendouyiyang";
        String result = getSQLTypeMedia(whereValue);
        JSONObject jsonObject = JSONObject.parseObject(result);
        String status = jsonObject.getString("status");
        if (!POST_RESULT_STATUS_SUCCESS.equals(status)) {
            throw new Exception("value not find: " + whereValue);
        }
        JSONObject dataJsonObject = jsonObject.getJSONObject("data");
        String newValue = dataJsonObject.getString(whereValue);
        List<String> newValueList = new ArrayList<>();
        newValueList.add(newValue);
        jsqlParserHelper.setTreeNodeValueValue(whereKey, newValueList);
    }

    private String getSQLTypeMedia(String value) throws Exception {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put(value, "");
        List<NameValuePair> params = new ArrayList<>();
        params.add(new BasicNameValuePair("scope", "urlmediaid"));
        params.add(new BasicNameValuePair("opration", "search"));
        params.add(new BasicNameValuePair("content", ""));
        params.add(new BasicNameValuePair("condition", jsonObject.toString()));

        //{"data":{"showchannel":"wxshow","srctable":"qfsrc","srcchannel":"wxsrc","cols":[{"srccolumn":"gt","showcolumn":"getTime"},{"srccolumn":"it","showcolumn":"insertTime"}],"showtable":"qushow"},"detail":"find msg ok","status":"succeeded"}
        String result = "";
        try {
            result = HttpHelper.httpPost(POST_CHANGE_NAME_URL, params);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getFullStackTrace(e));
            throw new Exception("get sql type media http error.");
        }

        return result;
    }

    private void replaceSQLWithNewName() throws Exception {
        String tableName = jsqlParserHelper.getSQLTables().get(0);
        String[] channelTable = tableName.split("\\.");
        String channel = channelTable[0];
        String table = channelTable[1];
        List<String> selectItems = jsqlParserHelper.getSQLSelectItems();
        List<String> whereKeys = jsqlParserHelper.getSQLWhereKeys();


        List<String> searchItems = null;
        Boolean hasSearchKey = whereKeys.indexOf(WHERE_SEARCH_KEY) != -1;
        if (hasSearchKey) {
            searchItems = getSearchValueItems();
            whereKeys.addAll(searchItems);
            whereKeys.remove(WHERE_SEARCH_KEY);
        }

        List<String> items = new ArrayList<>();
        items.addAll(selectItems);
        items.addAll(whereKeys);
        postResult = getSQLColumnsAgainst(channel, table, items);
//        String postResult = "{\"data\":{\"showchannel\":\"tongdao\",\"srctable\":\"lx\",\"srcchannel\":\"td\",\"cols\":[{\"srccolumn\":\"c_dxnrsrc1\",\"showcolumn\":\"c_dxnr1\"}, {\"srccolumn\":\"c_dxnrsrc\",\"showcolumn\":\"c_dxnr\"}, {\"srccolumn\":\"getTime\",\"showcolumn\":\"gt\"},{\"srccolumn\":\"m_publish_time\",\"showcolumn\":\"pt\"}],\"showtable\":\"leixing\"},\"detail\":\"find msg ok\",\"status\":\"succeeded\"}";
//        String postResult = "{\"data\":{\"showchannel\":\"wxshow\",\"srctable\":\"z_cu\",\"srcchannel\":\"wxsrc\",\"cols\":[{\"srccolumn\":\"pc\",\"showcolumn\":\"postchannel\"},{\"srccolumn\":\"pi\",\"showcolumn\":\"postid\"},{\"srccolumn\":\"mt\",\"showcolumn\":\"msg_type\"},{\"srccolumn\":\"d\",\"showcolumn\":\"domain\"},{\"srccolumn\":\"ln\",\"showcolumn\":\"likenum\"},{\"srccolumn\":\"k\",\"showcolumn\":\"key\"}],\"showtable\":\"zhj_commentUpdate\"},\"detail\":\"find msg ok\",\"status\":\"succeeded\"}";

        //消息队列用的
//        postResult = "{\"data\":{\"showchannel\":\"wxshow\",\"srctable\":\"zhj_commentUpdate\",\"srcchannel\":\"wxsrc\",\"cols\":[{\"srccolumn\":\"postchannel\",\"showcolumn\":\"pc\"},{\"srccolumn\":\"postid\",\"showcolumn\":\"pi\"},{\"srccolumn\":\"msg_type\",\"showcolumn\":\"mt\"},{\"srccolumn\":\"domain\",\"showcolumn\":\"d\"},{\"srccolumn\":\"likenum\",\"showcolumn\":\"ln\"},{\"srccolumn\":\"key\",\"showcolumn\":\"k\"}],\"showtable\":\"z_cu\"},\"detail\":\"find msg ok\",\"status\":\"succeeded\"}" ;
        //mpp数据库用的
//        postResult = "{\"data\":{\"showchannel\":\"wxshow\",\"srctable\":\"test1\",\"srcchannel\":\"wxsrc\",\"cols\":[{\"srccolumn\":\"g_ch_key\",\"showcolumn\":\"key\"},{\"srccolumn\":\"g_id\",\"showcolumn\":\"id\"},{\"srccolumn\":\"m_publish_time\",\"showcolumn\":\"time\"},{\"srccolumn\":\"tags\",\"showcolumn\":\"tags\"},{\"srccolumn\":\"g_ch_key,g_id,m_publish_time,tags\",\"showcolumn\":\"*\"}],\"showtable\":\"t\"},\"detail\":\"find msg ok\",\"status\":\"succeeded\"}";
        JSONObject jsonPostResult = JSONObject.parseObject(postResult);
        String status = getPostResultStatus(jsonPostResult);
        String detail = getPostResultDetail(jsonPostResult);
        if (!POST_RESULT_STATUS_SUCCESS.equals(status)) {
            throw new Exception("status: " + status + " detail: " + detail);
        }

        JSONObject jsonDataColumn = getPostResultData(jsonPostResult);
        Map<String, String> channelMap = getPostResultChannelMap(jsonDataColumn);
        Map<String, String> tableMap = getPostResultTableMap(jsonDataColumn);
        Map<String, String> selectItemsMap = getPostResultSelectItemsMap(jsonDataColumn, selectItems);
        Map<String, String> whereItemsMap = getPostResultWhereItemsMap(jsonDataColumn, whereKeys);

        replaceTable(channel, channelMap, table, tableMap);
        replaceSelectItems(selectItems, selectItemsMap);
        replaceWhereItems(whereItemsMap);
        if (hasSearchKey) {
            replaceWhereSearchKeyValueKey(searchItems, whereItemsMap);
        }
    }

    private List<String> getSearchValueItems() {
        List<String> searchItems = new ArrayList<>();
        List<CondTreeData> condTreeDataList = jsqlParserHelper.filterTreeNode(WHERE_SEARCH_KEY);
        for (CondTreeData condTreeData : condTreeDataList) {
            String searchValueString = condTreeData.getValue().getValue();
            String[] searchValueStringList = searchValueString.split(" ");
            for (String searchValue : searchValueStringList) {
                String searchValueKey = searchValue.split(":")[0];
                Boolean needClean = (searchValueKey.startsWith("'") && searchValueKey.endsWith("'")) || (searchValueKey.startsWith("\"") && searchValueKey.endsWith("\""));
                if (needClean) {
                    searchValueKey = searchValueKey.substring(1, searchValueKey.length() - 1);
                }
                searchValueKey = searchValueKey.substring(1, searchValueKey.length());
                searchItems.add(searchValueKey);
            }
        }

        return searchItems;
    }

    private String getSQLColumnsAgainst(String channel, String table, List<String> items) throws Exception {

        List<Map<String, String>> columnMaps = getPostConditionParam(items);
        JSONObject jsonContentObject = new JSONObject();
        jsonContentObject.put("showchannel", channel);
        jsonContentObject.put("showtable", table);
        jsonContentObject.put("cols", columnMaps);
        List<NameValuePair> params = new ArrayList<>();
        params.add(new BasicNameValuePair("scope", "resource"));
        params.add(new BasicNameValuePair("opration", "search"));
        params.add(new BasicNameValuePair("content", ""));
        params.add(new BasicNameValuePair("condition", jsonContentObject.toString()));

        //{"data":{"showchannel":"wxshow","srctable":"qfsrc","srcchannel":"wxsrc","cols":[{"srccolumn":"gt","showcolumn":"getTime"},{"srccolumn":"it","showcolumn":"insertTime"}],"showtable":"qushow"},"detail":"find msg ok","status":"succeeded"}
        String result = "";
        try {
            result = HttpHelper.httpPost(POST_CHANGE_NAME_URL, params);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getFullStackTrace(e));
            throw new Exception("get sql columns http error");
        }

        return result;
    }

    private List<Map<String, String>> getPostConditionParam(List<String> items) throws Exception {
        List<Map<String, String>> columnMaps = new ArrayList<>();

        for (String item : items) {
            Map<String, String> columnMap = new HashMap<>();
            columnMap.put(ITEM_TYPE, item);
            columnMaps.add(columnMap);
        }

        return columnMaps;
    }

    private String getPostResultStatus(JSONObject jsonObject) {
        return jsonObject.getString(POST_RESULT_STATUS);
    }

    private String getPostResultDetail(JSONObject jsonObject) {
        return jsonObject.getString(POST_RESULT_DETAIL);
    }

    private JSONObject getPostResultData(JSONObject jsonObject) {
        return jsonObject.getJSONObject(POST_RESULT_DATA);
    }

    private Map<String, String> getPostResultChannelMap(JSONObject jsonObject) {
        Map<String, String> map = new HashMap<>();
        map.put(jsonObject.getString("showchannel"), jsonObject.getString("srcchannel"));
        return map;
    }

    private Map<String, String> getPostResultTableMap(JSONObject jsonObject) {
        Map<String, String> map = new HashMap<>();
        map.put(jsonObject.getString("showtable"), jsonObject.getString("srctable"));
        return map;
    }

    private Map<String, String> getPostResultItemsMap(JSONObject jsonObject) {
        Map<String, String> map = new HashMap<>();
        JSONArray jsonArray = jsonObject.getJSONArray("cols");

        for (Object cell : jsonArray) {
            JSONObject jsonCell = JSONObject.parseObject(String.valueOf(cell));
            map.put(jsonCell.getString("showcolumn"), jsonCell.getString("srccolumn"));
        }
        return map;
    }

    private Map<String, String> getPostResultSelectItemsMap(JSONObject jsonObject, List<String> selectItems) {
        Map<String, String> map = new HashMap<>();
        Map<String, String> itemsMap = getPostResultItemsMap(jsonObject);

        for (String oldItem : selectItems) {
            String newItem = itemsMap.get(oldItem);
            map.put(oldItem, newItem);
        }
        return map;
    }

    private Map<String, String> getPostResultWhereItemsMap(JSONObject jsonObject, List<String> whereItems) {
        Map<String, String> map = new HashMap<>();
        Map<String, String> itemsMap = getPostResultItemsMap(jsonObject);

        for (String oldItem : whereItems) {
            String newItem = itemsMap.get(oldItem);
            map.put(oldItem, newItem);
        }
        return map;
    }

    private void replaceTable(String channel, Map<String, String> channelMap, String table, Map<String, String> tableMap) {
        List<String> newTables = new ArrayList<>();
        String newChannel = channelMap.get(channel);
        String newTable = tableMap.get(table);
        String result = newChannel + "." + newTable;
        newTables.add(result);
        jsqlParserHelper.setSQLTables(newTables);
    }

    private void replaceSelectItems(List<String> oldSelectItems, Map<String, String> selectItemsMap) {
        List<String> newSelectItems = new ArrayList<>();

        for (String oldSelectItem : oldSelectItems) {
            String newSelectItem = selectItemsMap.get(oldSelectItem);
            newSelectItems.add(newSelectItem);
        }

        jsqlParserHelper.setSQLSelectItems(newSelectItems);
    }

    private void replaceWhereItems(Map<String, String> whereItemsMap) throws Exception {
        for (Map.Entry<String, String> entry : whereItemsMap.entrySet()) {
            String oldWhereItem = entry.getKey();
            String newWhereItem = entry.getValue();
            jsqlParserHelper.setTreeNodeValueKey(oldWhereItem, newWhereItem);
        }
    }

    private void replaceWhereSearchKeyValueKey(List<String> searchItems, Map<String, String> whereItemsMap) throws Exception {
        List<String> searchValueListOld = new ArrayList<>();
        List<String> searchValueListNew = new ArrayList<>();

        List<CondTreeData> condTreeDataList = jsqlParserHelper.filterTreeNode(WHERE_SEARCH_KEY);
        for (CondTreeData condTreeData : condTreeDataList) {
            String searchValue = condTreeData.getValue().getValue();
            searchValueListOld.add(searchValue);
        }

        StringBuilder sb = new StringBuilder();
        sb.append("((");
        for (String searchValueOld : searchValueListOld) {
            String[] searchValueOldList = searchValueOld.split(" ");
            for (String subSearchValueOld : searchValueOldList) {
                String searchValueOldKey = subSearchValueOld.split(":")[0];
                Boolean needClean = (searchValueOldKey.startsWith("'") && searchValueOldKey.endsWith("'")) || (searchValueOldKey.startsWith("\"") && searchValueOldKey.endsWith("\""));
                if (needClean) {
                    searchValueOldKey = searchValueOldKey.substring(1, searchValueOldKey.length() - 1);
                }
                String symbol = searchValueOldKey.substring(0, 1);
                searchValueOldKey = searchValueOldKey.substring(1, searchValueOldKey.length());
                String searchValueOldValue = subSearchValueOld.split(":")[1];
                for (String searchItem : searchItems) {
                    if (searchValueOldKey.equals(searchItem)) {
                        String searchValueNewKey = searchValueOldKey.replace(searchItem, whereItemsMap.get(searchItem));
//                        searchValueListNew.add(searchValueNewKey + ":" + searchValueOldValue);
                        sb.append(symbol);
                        sb.append(searchValueNewKey);
                        sb.append(":");
                        sb.append(searchValueOldValue);
                        sb.append(" ");
                    }
                }
            }
        }
        sb.append("))");
        searchValueListNew.add(sb.toString());

        jsqlParserHelper.setTreeNodeValueValue(WHERE_SEARCH_KEY, searchValueListNew);

    }

    //TODO
    private Boolean checkPermissionRight() throws Exception {
        if (true) {
            return true;
        }
        String table = jsqlParserHelper.getSQLTables().get(0);
        String[] source_list = table.split("\\.");
        if (source_list.length != 3) {
            errorMsg += "table name error";
            return false;
        }

        String sourceItemResult = getSourceItemFromServer(source_list);
        if (sourceItemResult.length() == 0) {
            return false;
        }

        JSONObject sourceItemJsonResult = JSONObject.parseObject(sourceItemResult);
        if ("fail".equals(sourceItemJsonResult.getString("status"))) {
            errorMsg += sourceItemJsonResult.getString("result");
            return false;
        } else if ("success".equals(sourceItemJsonResult.getString("status"))) {
            if (sourceItemJsonResult.getJSONArray("result").size() == 0) {
                errorMsg += "table error. ";
                return false;
            }
            String[] returnSqlItems = (String[]) sourceItemJsonResult.getJSONArray("result").toArray(new String[sourceItemJsonResult.getJSONArray("result").size()]);
            List<String> returnSqlItemList = Arrays.asList(returnSqlItems);
            Boolean selectItemPermit = checkSelectItems(returnSqlItemList);
            if (!selectItemPermit) {
                return false;
            }
            Boolean whereItemPermit = checkWhereItems(returnSqlItemList);
            if (!whereItemPermit) {
                return false;
            }

            String agencyProvinceResult = getAgencyProvinceFromServer();
            JSONObject agencyProvinceJsonResult = JSONObject.parseObject(agencyProvinceResult);
            if ("fail".equals(agencyProvinceJsonResult.getString("status"))) {
                errorMsg += agencyProvinceJsonResult.getString("result");
                return false;
            } else if ("success".equals(agencyProvinceJsonResult.getString("status"))) {
                if (agencyProvinceJsonResult.getJSONArray("result").size() == 0) {
                    errorMsg += "table error. ";
                    return false;
                }

                String[] returnAgencyProvince = (String[]) agencyProvinceJsonResult.getJSONArray("result").toArray(new String[sourceItemJsonResult.getJSONArray("result").size()]);
                List<String> returnAgencyProvinceList = Arrays.asList(returnAgencyProvince);

                Boolean agencyProvincePermit = checkAgencyProvince(returnAgencyProvinceList);
                return agencyProvincePermit;
            } else {
                errorMsg += "get agency province return status error. ";
                return false;
            }
        } else {
            errorMsg += "get source item return status error. ";
            return false;
        }
    }

    private String getSourceItemFromServer(String[] sourceList) throws Exception {
        String opinionField = sourceList[0];
        String sourceTunnel = sourceList[1];
        String sourceType = sourceList[2];

        List<NameValuePair> params = new ArrayList<>();
        params.add(new BasicNameValuePair("agency_id", this.uid));
        params.add(new BasicNameValuePair("opinion_field", opinionField));
        params.add(new BasicNameValuePair("source_tunnel", sourceTunnel));
        params.add(new BasicNameValuePair("source_type", sourceType));

        return HttpHelper.httpPost(POST_GET_SOURCE_ITEM_URL, params);
    }

    private Boolean checkSelectItems(List<String> returnItems) throws Exception {
        List<String> selectItems = jsqlParserHelper.getSQLSelectItems();
        for (String item : selectItems) {
            if (-1 == returnItems.indexOf(item)) {
                errorMsg += "select columns error. ";
                return false;
            }
        }
        return true;
    }

    private Boolean checkWhereItems(List<String> returnItems) throws Exception {
        List<String> whereItems = jsqlParserHelper.getSQLWhereKeys();
        for (String item : whereItems) {
            if ((!PROVINCE_COLUMN.equals(item)) && -1 == returnItems.indexOf(item)) {
                errorMsg += "where columns error. ";
                return false;
            }
        }
        return true;
    }

    private String getAgencyProvinceFromServer() throws Exception {
        List<NameValuePair> params = new ArrayList<>();
        params.add(new BasicNameValuePair("agency_id", this.uid));

        return HttpHelper.httpPost(POST_GET_AGENCY_PROVINCE_URL, params);
    }

    private Boolean checkAgencyProvince(List<String> returnItems) throws Exception {
        //一会改名字
        List<Map<String, String>> whereItemMapList = jsqlParserHelper.getSQLWhereMapList();
        for (Map<String, String> whereItemMap : whereItemMapList) {
            String key = whereItemMap.get(PROVINCE_COLUMN);
            if (key != null) {
                if (returnItems.indexOf(key) == -1) {
                    errorMsg += "agency province error. ";
                    return false;
                }
            }
        }

        return true;
    }

}
